﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using VswService_V7.Utility;

namespace VswService_V7.DataBase.Tables
{
    /// <summary>
    /// wp code
    /// </summary>
    public partial class BUNDLE_TABLE
    {
        public static int pageNum = 20;
        /// <summary>
        /// bundleno pagination for fuzzy query of bundle information
        /// </summary>
        /// <param name="bundleNo"></param>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <param name="TotalPages"></param>
        /// <returns></returns>
        public static DataTable QueryBundleByBundleNo(string bundleNo, int minRow, int maxRow, out string TotalPages)
        {
            try
            {
                string sql = string.Format("select * from (select rownum rn,a.* from (select BUNDLE_NO from BUNDLE_TABLE where BUNDLE_NO like '%{0}%' group by BUNDLE_NO) a) where rn>{1} and rn <={2}",
                   bundleNo, minRow, maxRow);
                string sqlCount = string.Format("select count(*) from (select BUNDLE_NO from BUNDLE_TABLE where BUNDLE_NO like '%{0}%' group by BUNDLE_NO)",
                  bundleNo);
                DataTable table = OracleHelper.Exct_QueryTable(sql);
                if (table != null)
                {
                    int totalCounts = Convert.ToInt32(TASK_INFO.GetTableCount(sqlCount));
                    if (totalCounts % pageNum == 0)
                    {
                        TotalPages = (totalCounts / pageNum).ToString();
                    }
                    else
                    {
                        TotalPages = (totalCounts / pageNum + 1).ToString();
                    }
                    LogHelper.WriteLog("QueryBundleByBundleNo Success");
                    return table;
                }
                else
                {
                    TotalPages = "";
                    return null; ;
                }

            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("QueryBundleByBundleNo", ex);
                throw ex;
            }
        }
        /// <summary>
        /// BundleNo for query bundle and pipe information
        /// </summary>
        /// <param name="bundleNo"></param>
        /// <param name="dic"></param>
        /// <returns></returns>
        public static Dictionary<string, DataTable> QueryBundlePipeByBundleNo(string bundleNo, out Dictionary<string, string> dic, out DataTable dicBundleTotal)
        {
            try
            {
                string bundleSql = string.Format("select * from BUNDLE_TABLE where BUNDLE_NO='{0}' order by UPDATE_TIME desc", bundleNo);
                DataTable bundleTable = OracleHelper.Exct_QueryTable(bundleSql);
                DateTime bundleUpdateTime = new DateTime();
                string date = "";
                string time = "";
                string cradle = "";
                if (bundleTable.Rows.Count > 0)
                {
                    bundleUpdateTime = Convert.ToDateTime(bundleTable.Rows[0][fd_updateTime].ToString());
                    date = bundleUpdateTime.ToString(Formats.GetShortFormat());
                    time = bundleUpdateTime.ToString(Formats.GetTimeFormat());
                    cradle = bundleTable.Rows[0][fd_cradle].ToString();
                }

                string recipeSql = string.Format("select * from recipe_info where RECIPE_NO in (select b.RECIPE_NO from TASK_INFO b where TASK_NO in (select a.TASK_NO from PIPE_INFO a where a.pipe_no in (select c.pipe_no from BUNDLE_TABLE c where BUNDLE_NO='{0}')))", bundleNo);
                DataTable recipeTable = OracleHelper.Exct_QueryTable(recipeSql);
                string pipeOD = "0";
                string stripThickness = "0";
                if (recipeTable.Rows.Count == 1)
                {
                    pipeOD = recipeTable.Rows[0][RECIPE_INFO.fd_pipeOd].ToString();//单位：mm					
                    stripThickness = recipeTable.Rows[0][RECIPE_INFO.fd_stripThickness].ToString();//单位：mm
                }

                string taskSql = string.Format("select * from TASK_INFO b where TASK_NO in (select a.TASK_NO from PIPE_INFO a where a.pipe_no in (select c.pipe_no from BUNDLE_TABLE c where BUNDLE_NO='{0}'))", bundleNo);
                DataTable taskTable = OracleHelper.Exct_QueryTable(taskSql);
                string productionStandard = "";
                string pipeDurabilityClass = "";
                string pipeSteelGrade = "";
                if (taskTable.Rows.Count == 1)
                {
                    productionStandard = taskTable.Rows[0][TASK_INFO.fd_productionStandard].ToString();
                    pipeDurabilityClass = taskTable.Rows[0][TASK_INFO.fd_pipeDurabilityClass].ToString();
                    pipeSteelGrade = taskTable.Rows[0][TASK_INFO.fd_pipeSteelGrade].ToString();
                }

                string batchNoSql = string.Format("select d.batch_no from (select * from (select c.* from PIPE_INFO c,(select * from BUNDLE_TABLE a where a.BUNDLE_NO='{0}') b where b.pipe_no=c.PIPE_NO and b.cradle=c.location)) d group by d.BATCH_NO", bundleNo);
                DataTable batchTable = OracleHelper.Exct_QueryTable(batchNoSql);
                string batchNos = "";
                if (batchTable.Rows.Count > 0)
                {
                    for (int i = 0; i < batchTable.Rows.Count; i++)
                    {
                        if (i == 0)
                        {
                            batchNos += batchTable.Rows[i][PIPE_INFO.fd_batchNo].ToString();
                        }
                        else
                        {
                            batchNos += "," + batchTable.Rows[i][PIPE_INFO.fd_batchNo].ToString();
                        }
                    }
                }

                string pipeCountsSql = string.Format("select count(*) from BUNDLE_TABLE where BUNDLE_NO='{0}' order by UPDATE_TIME desc", bundleNo);
                string pipeCounts = TASK_INFO.GetTableCount(pipeCountsSql);

                string bundleLengthSql = string.Format("select sum(length) from (select c.* from PIPE_INFO c,(select * from BUNDLE_TABLE a where a.BUNDLE_NO='{0}') b where b.pipe_no=c.PIPE_NO and b.cradle=c.location)", bundleNo);
                double bundleLength = Math.Round(Convert.ToDouble(PIPE_INFO.getNumber(bundleLengthSql)) * 0.001, 3);//单位：m

                //计算bundle的质量				
                double density = 7850;//单位：kg/m3
                double pipeOd = Convert.ToDouble(pipeOD) * 0.001;//单位：m
                double stripTHK = Convert.ToDouble(stripThickness) * 0.001;//单位：m
                double bundleWeight = Math.Round(density * 3.14 * (pipeOd * stripTHK - stripTHK * stripTHK) * bundleLength, 3);

                string shiftID = TEAM_TABLE.GetCurrentShift(bundleUpdateTime);
                dic = new Dictionary<string, string>();
                dic.Clear();
                dic.Add(fd_bundleNo, bundleNo);
                dic.Add("DATE", date);
                dic.Add("TIME", time);
                dic.Add("PIPEODWT", pipeOD + "x" + stripThickness);
                dic.Add(TASK_INFO.fd_productionStandard, productionStandard);
                dic.Add(TASK_INFO.fd_pipeDurabilityClass, pipeDurabilityClass);
                dic.Add(TASK_INFO.fd_pipeSteelGrade, pipeSteelGrade);
                dic.Add("BATCH_NUMBERS", batchNos);
                dic.Add("PIPE_QUANLITY", pipeCounts);
                dic.Add("TOTAL_LENGTH", bundleLength.ToString());
                dic.Add("BUNDLE_WEIGHT", bundleWeight.ToString());
                dic.Add("SHIFT_ID", shiftID);
                dic.Add(fd_cradle, cradle);

                //DataTable batchPipeTable = new DataTable();
                //batchPipeTable.Columns.Add("Pipe ID", typeof(string));
                //batchPipeTable.Columns.Add("Length", typeof(string));
                //batchPipeTable.Columns.Add("Weight", typeof(string));
                //batchPipeTable.Columns.Add("Defect code", typeof(string));

                Dictionary<string, DataTable> dicBundle = new Dictionary<string, DataTable>();
                dicBundle.Clear();
                dicBundleTotal = new DataTable();
                dicBundleTotal.Columns.Add("Counts", typeof(string));
                dicBundleTotal.Columns.Add("Length(M)", typeof(string));
                dicBundleTotal.Columns.Add("Weight(Kg)", typeof(string));

                double bundleTotalCounts = 0;
                double bundleTotalLength = 0;
                double bundleTotalWeight = 0;
                if (batchTable.Rows.Count > 0)
                {
                    for (int i = 0; i < batchTable.Rows.Count; i++)
                    {
                        DataTable batchPipeTable = new DataTable();
                        batchPipeTable.Columns.Add("Pipe ID", typeof(string));
                        batchPipeTable.Columns.Add("Length", typeof(string));
                        batchPipeTable.Columns.Add("Weight", typeof(string));
                        batchPipeTable.Columns.Add("Defect code", typeof(string));
                        batchPipeTable.Clear();
                        string sql = string.Format("select * from (select c.* from PIPE_INFO c,(select * from BUNDLE_TABLE a where a.BUNDLE_NO='{0}') b where b.pipe_no=c.PIPE_NO and b.cradle=c.location) d where d.batch_no='{1}' order by d.update_time desc", bundleNo, batchTable.Rows[i][PIPE_INFO.fd_batchNo].ToString());
                        DataTable pipeTable = OracleHelper.Exct_QueryTable(sql);
                        if (pipeTable.Rows.Count > 0)
                        {
                            double totalCounts = pipeTable.Rows.Count;
                            double totalLength = 0;
                            double totalWeight = 0;
                            for (int j = 0; j < pipeTable.Rows.Count; j++)
                            {
                                string pipeid = pipeTable.Rows[j][fd_pipeNo].ToString();
                                double length = Convert.ToDouble(pipeTable.Rows[j][PIPE_INFO.fd_length].ToString()) * 0.001;//单位：m
                                totalLength += length;
                                string quatityCode = pipeTable.Rows[j][PIPE_INFO.fd_qualityCode].ToString();
                                double pipeWeight = Math.Round(density * 3.14 * (pipeOd * stripTHK - stripTHK * stripTHK) * length, 3);
                                totalWeight += pipeWeight;
                                batchPipeTable.Rows.Add(pipeid, length.ToString(), pipeWeight.ToString(), quatityCode);
                            }
                            batchPipeTable.Rows.Add("Total:" + totalCounts.ToString(), Math.Round(totalLength, 3).ToString(), totalWeight.ToString(), "");
                            bundleTotalCounts += totalCounts;
                            bundleTotalLength += totalLength;
                            bundleTotalWeight += totalWeight;
                        }
                        dicBundle.Add(batchTable.Rows[i][PIPE_INFO.fd_batchNo].ToString(), batchPipeTable);
                    }
                    dicBundleTotal.Rows.Add(Math.Round(bundleTotalCounts, 3).ToString(), Math.Round(bundleTotalLength, 3).ToString(), Math.Round(bundleTotalWeight, 3).ToString());
                }

                LogHelper.WriteLog("QueryBundlePipeByBundleNo Success");
                return dicBundle;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("QueryBundlePipeByBundleNo", ex);
                throw ex;
            }
        }
    }
}
